package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Util;

import orange.database.DBIns;

public class OwYieldandAreaconn extends BaseAction {
	public OwYieldandAreaconn(String[] str1, String[] str2, String[] str3) throws SQLException {
		super();
		String sql;
		ResultSet rs;
		Connection conn = DBIns.getInstance().getConnection();
		Statement stmt = conn.createStatement();
		sql = change2_1(str1, str2, str3);
		System.out.println(sql);
		rs = stmt.executeQuery(sql);
		value = combine2_1(rs);
		DBIns.getInstance().release(conn);

	}

	public String change2_1(String[] str1, String[] str2, String[] str3) {
		try {

			nianfen = Util.Convert(str1);

			guojia = Util.Convert(str2);

			int p = str3.length;
			pinzhong = Util.Convert(str3);

			 String ppinzhong = new String(str3[p - 1].getBytes("ISO-8859-1"),
					 "utf-8");
//			String ppinzhong = new String(str3[p - 1].getBytes());

			String sql = "1";
			if (ppinzhong.equals("所有品种"))
				sql = "select NianFen,GuoJia,PinZhong,ChangLiang,MianJi,EnglishName "
						+ "from orange_w_changliangandmianji,guojiamingcheng where guojiamingcheng.ChineseName=orange_w_changliangandmianji.GuoJia and orange_w_changliangandmianji.NianFen in("
						+ nianfen
						+ ") and orange_w_changliangandmianji.GuoJia in("
						+ guojia + ") order by NianFen";
			else
				sql = "select NianFen,GuoJia,PinZhong,ChangLiang,MianJi,EnglishName "
						+ "from orange_w_changliangandmianji,guojiamingcheng where guojiamingcheng.ChineseName=orange_w_changliangandmianji.GuoJia and orange_w_changliangandmianji.NianFen in ("
						+ nianfen
						+ ") and orange_w_changliangandmianji.GuoJia in ("
						+ guojia
						+ ") and orange_w_changliangandmianji.PinZhong in ("
						+ pinzhong + ")  order by NianFen";
			return sql;
		} catch (Exception e) {
			return "error";
		}
	}

	public String combine2_1(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份(Year)</td><td>国家(Country)</td><td>品种(Variety)</td><td>产量(Production)吨</td><td>面积(Area)公顷</td><td>单产(Yield)吨/公顷</td></tr></thead>");
		try {
			while (rs1.next()) {
				buffer1.append("<tr><td>"
						+ rs1.getObject("NianFen")
						+ "</td><td>"
						+ rs1.getObject("GuoJia")
						+ rs1.getObject("EnglishName")
						+ "</td><td>"
						+ rs1.getObject("PinZhong")
						+ "</td><td>"
						+ rs1.getString("ChangLiang")
						+ "</td><td>"
						+ rs1.getString("MianJi")
						+ "</td><td>"
						+ yeild(rs1.getString("ChangLiang"), rs1
								.getString("MianJi")) + "</td></tr>");
			}
			value = new String(buffer1);
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}

	public String yeild(String str1, String str2) {
		str1 = str1.trim();
		str2 = str2.trim();
		if (str1.equals("NAN") || str2.equals("NAN"))
			return NAN;
		else {
			float d = SToFloat(str1) / SToFloat(str2);
			String dan = d + "";
			return dan;
		}
	}

	public float SToFloat(String str) {
		char a[] = str.toCharArray();
		int n = str.length();
		int j = 0;
		float f1 = 0;
		if (str.equals("NAN"))
			return f1;
		char b[] = new char[30];
		for (int i = 0; i < n; i++)
			if (a[i] != ',')
				b[j++] = a[i];
		b[j] = 0;
		String str2 = new String(b);
		float f = Float.parseFloat(str2);
		return f;
	}

	public static void main(String[] arg) {
		try {
			String[] str1 = { "2007" }, str2 = { "马耳他" }, str3 = { "所有品种" };
			OwYieldandAreaconn ow = new OwYieldandAreaconn(str1, str2, str3);
			System.out.println(ow.getValue());
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
}
